﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using BiffenModel;

namespace BiffenServiceLibary
{
    [DataContract]
    public class BiffenDal : IBiffenDal
    {
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }

        #region Person
        public int InsertPerson(string fname, string lname, string street, string streetNo, int zipCode, string phone, string email)
        {
            try
            {
                string sql = string.Format("Insert into bifPerson" +
                     "(firstName,lastName,street,streetNo,zipCode,phoneNo,mail) Values" +
                     "('{0}', '{1}', '{2}', '{3}', {4},'{5}','{6}')", fname, lname, street, streetNo, zipCode, phone, email);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Person error " + se);

            }
            return GetMaxPersonId();
        }

        public int GetPersonIdFromCusID(int cusId)
        {
            int pId = -1;
            string sql = string.Format("Select persID from bifCustomer where customerID = {0}", cusId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(String.Format("{0}", dr["persID"]));
                    pId = (int)(dr["persID"]);
                }
                dr.Close();
            }
            return pId;
        }

        public int GetPersonIdFromCusPhone(string phone)
        {
            int pId = -1;
            string sql = string.Format("Select persID from bifCustomer where phoneNo = {0}", phone);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(String.Format("{0}", dr["persID"]));
                    pId = (int)(dr["persID"]);
                }
                dr.Close();
            }
            return pId;
        }

        public int GetPersonIdFromEmpID(int empId)
        {
            int pId = -1;
            string sql = string.Format("Select persID from bifEmployee where employeeID = {0}", empId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(String.Format("{0}", dr["persID"]));
                    pId = (int)(dr["persID"]);
                }
                dr.Close();
            }
            return pId;
        }


        public int GetMaxPersonId()
        {
            try
            {
                int pId = -1;
                string sql = "Select MAX(personID) as maxID from bifPerson";
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        pId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return pId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }

        //public int getMaxEmployeeId()
        //{
        //    try
        //    {
        //        int pId = -1;
        //        string sql = "Select MAX(employeeID) as maxID from bifEmployee";
        //        using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
        //        {
        //            SqlDataReader dr = cmd.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                Console.WriteLine(String.Format("{0}", dr["maxID"]));
        //                pId = (int)(dr["maxID"]);
        //            }
        //            dr.Close();
        //        }
        //        return pId;
        //    }
        //    catch (SqlException se2)
        //    {
        //        Console.WriteLine("!SQL fejl " + se2);
        //        return -1;
        //    }
        //}

        public void InsertCustomer(string fname, string lname, string street, string streetNo, int zipCode, string phone, DateTime birthDate, string email)
        {
            try
            {
                int personID = InsertPerson(fname, lname, street, streetNo, zipCode, phone, email);
                string sql = string.Format("Insert into bifCustomer" +
                "(persID, birthDate) Values" +
                "('{0}', '{1}')", personID, birthDate);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se3)
            {
                Console.WriteLine("!Fejl inserting customer " + se3);
            }
        }

        public void InsertEmployee(string fname, string lname, string street, string streetNo, int zipCode, string phone, string userName, string password, string email)
        {
            try
            {
                int personID = InsertPerson(fname, lname, street, streetNo, zipCode, phone, email);
                string sql = string.Format("Insert into bifEmployee" +
                "(persID, username,password) Values" +
                "('{0}', '{1}','{2}]')", personID, userName, password);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se4)
            {
                Console.WriteLine("!Fejl inserting Employee " + se4);

            }
            finally
            {
                sqlCn.Close();
            }
        }

        public void DeleteCustomer(int cusId, int persId)
        {
            SqlTransaction myTrans = sqlCn.BeginTransaction();

            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = sqlCn;
            myCommand.Transaction = myTrans;
            try
            {
                myCommand.CommandText = string.Format("Delete from bifCustomer where employeeID  = '{0}'", cusId);
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = string.Format("Delete from bifPerson where personID = {0}", persId);
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Sorry! Customer not deleted! " + ex);
                myTrans.Rollback();

            }
        }

        public void DeleteEmployee(int empId, int persId)
        {
            SqlTransaction myTrans = sqlCn.BeginTransaction();

            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = sqlCn;
            myCommand.Transaction = myTrans;
            try
            {
                myCommand.CommandText = string.Format("Delete from bifEmployee where employeeID  = '{0}'", empId);
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = string.Format("Delete from bifPerson where personID = {0}", persId);
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Sorry! Employee not deleted! " + ex);
                myTrans.Rollback();

            }
        }

        public void EditCustomer(int id, string fname, string lname, string street, string streetNo, int zipCode, string phone, DateTime birthDate, string email)
        {
            string sql = string.Format("Update bifCustomer Set (firstName = '{0}', lastName = '{1}', street = '{2}', streetNo = '{3}', zipCode = {4}, phone = '{5}', birthDate = '{6}', mail = '{7}') Where Id = {8}"
                , fname, lname, street, streetNo, zipCode, phone, birthDate, email, id);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public void EditEmployee(int id, string fname, string lname, string street, string streetNo, int zipCode, string phone, string userName, string password, string email)
        {
            string sql = string.Format("Update bifEmployee Set (firstName = '{0}', lastName = '{1}', street = '{2}', streetNo = '{3}', zipCode = {4}, phone = '{5}', userName = '{6}', password = '{7}') Where Id = {8}"
                , fname, lname, street, streetNo, zipCode, phone, userName, password, id);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public List<Employee> AllEmployee()
        {
            List<Employee> inv = new List<Employee>();
            string sql = "Select * From bifEmployee E , bifPerson P, bifPostal PO where P.personId = E.persId and PO.zipCode = P.zipCode";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Employee(
                                (int)(dr["employeeID"]),
                                (string)(dr["firstName"]),
                                (string)(dr["lastName"]),
                                (string)(dr["street"]),
                                (string)(dr["streetNo"]),
                                (string)(dr["city"]),
                                (int)(dr["zipCode"]),
                                (string)(dr["phoneNo"]),
                                (string)(dr["mail"]),
                                (string)(dr["userName"]),
                                (string)(dr["password"])));
                }
                dr.Close();
            }
            return inv;
        }
        private int getMAXMovieID()
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(movieID) as maxID from bifMovie";
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }

        public List<Customer> AllCustomers()
        {
            List<Customer> inv = new List<Customer>();
            string sql = "Select * From bifCustomer C , bifPerson P, bifPostal PO where P.personId = C.persId and PO.zipCode = P.zipCode";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Customer(
                                (int)(dr["customerID"]),
                                (string)(dr["firstName"]),
                                (string)(dr["lastName"]),
                                (string)(dr["street"]),
                                (string)(dr["streetNo"]),
                                (string)(dr["city"]),
                                (int)(dr["zipCode"]),
                                (string)(dr["phoneNo"]),
                                (string)(dr["mail"]),
                                (DateTime)(dr["birthDate"])
                ));
                }
                dr.Close();
            }
            return inv;
        }

        public Customer GetOneCustomer(int cusId)
        {
            Customer _customer = new Customer();
            string sql = string.Format("Select * from bifCustomer C , bifPerson P, bifPostal PO where PO.zipCode = P.zipCode and P.personID = C.persID and C.customerID = '{0}'", cusId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _customer = (new Customer(
                                (int)(dr["customerID"]),
                                (string)(dr["firstName"]),
                                (string)(dr["lastName"]),
                                (string)(dr["street"]),
                                (string)(dr["streetNo"]),
                                (string)(dr["city"]),
                                (int)(dr["zipCode"]),
                                (string)(dr["phoneNo"]),
                                (string)(dr["mail"]),
                                (DateTime)(dr["birthDate"])));
                }
                dr.Close();
            }
            return _customer;
        }

        public Employee GetOneEmployee(int empId)
        {
            Employee _employee = new Employee();
            string sql = string.Format("Select * from bifEmployee E , bifPerson P, bifPostal PO where PO.zipCode = P.zipCode and P.personID = E.persID and E.employeeID = '{0}'", empId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _employee = (new Employee(
                                (int)(dr["employeeID"]),
                                (string)(dr["firstName"]),
                                (string)(dr["lastName"]),
                                (string)(dr["street"]),
                                (string)(dr["streetNo"]),
                                (string)(dr["city"]),
                                (int)(dr["zipCode"]),
                                (string)(dr["phoneNo"]),
                                (string)(dr["mail"]),
                                (string)(dr["userName"]),
                                (string)(dr["password"])));
                }
                dr.Close();
            }
            return _employee;
        }

        public Employee GetEmployeeUserName(string userName)
        {
            Employee _employee = new Employee();
            string sql = string.Format("Select * from bifEmployee E , bifPerson P, bifPostal PO where PO.zipCode = P.zipCode and P.personID = E.persID and E.employeeID = '{0}'", userName);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _employee = (new Employee(
                                (int)(dr["employeeID"]),
                                (string)(dr["firstName"]),
                                (string)(dr["lastName"]),
                                (string)(dr["street"]),
                                (string)(dr["streetNo"]),
                                (string)(dr["city"]),
                                (int)(dr["zipCode"]),
                                (string)(dr["phoneNo"]),
                                (string)(dr["mail"]),
                                (string)(dr["userName"]),
                                (string)(dr["password"])));
                }
                dr.Close();
            }
            return _employee;
        }
        #endregion

        #region Movie
        public int InsertMovie(string title, string director, string description, string genre, string timeLength, string picture)
        {
            try
            {
                string sql = string.Format("Insert into bifMovie" +
                     "(title,description,genre,timeLength,director,picture) Values" +
                     "('{0}', '{1}', '{2}', '{3}','{4}','{5}')", title, description, genre, timeLength, director, picture);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Movie error " + se);

            }
            return getMAXMovieID();
        }

        public void addArtistsToMovie(int movieID, List<string> artists)
        {
            try
            {
                foreach (string a in artists)
                {
                    string sql = string.Format("Insert into bifArtist (movieID,artistName) Values ({0},'{1}')", movieID, a);
                    using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Artist error " + se);

            }
        }


        public void EditMovie(int id, string title, string description, string genre, string timeLength)
        {
            string sql = string.Format("Update bifMovie Set (title = '{0}', description = '{1}', genre = '{2}', timeLength = '{3}') Where Id = {4}"
                , title, description, genre, timeLength, id);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public List<Movie> AllStateMovies(bool state)
        {
            int intState = convertState(state);

            List<Movie> inv = new List<Movie>();
            string sql = string.Format("Select * From bifMovie M, bifArtist A where A.movID = M.movieID and M.active = {0}", intState);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Movie(
                        (int)(dr["movieID"]),
                        (string)(dr["title"]),
                        (string)(dr["description"]),
                        (string)(dr["genre"]),
                        (string)(dr["timeLenght"]),
                        (string)(dr["director"]),
                        (string)(dr["picture"])));

                    //ARTISTER KOMMER IKKE MED I MOVIE::: FIKS :)
                }
                dr.Close();
            }
            return inv;

        }

        public List<Movie> AllMovies()
        {
            List<Movie> inv = new List<Movie>();
            string sql = string.Format("Select * From bifMovie M, bifArtist A where A.movID = M.movieID");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Movie(
                        (int)(dr["movieID"]),
                        (string)(dr["title"]),
                        (string)(dr["description"]),
                        (string)(dr["genre"]),
                        (string)(dr["timeLenght"]),
                        (string)(dr["director"]),
                        (string)(dr["picture"])));

                    //ARTISTER KOMMER IKKE MED I MOVIE::: FIKS :)
                }
                dr.Close();
            }
            return inv;
        }

        public int getShowIdFromMovieID(int movID)
        {
            int pId = -1;
            string sql = string.Format("Select showID from bifShow where movID = {0}", movID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(String.Format("{0}", dr["showID"]));
                    pId = (int)(dr["showID"]);
                }
                dr.Close();
            }
            return pId;
        }

        public Movie getOneMovie(int movID)
        {
            Movie _movie = new Movie();
            string sql = string.Format("Select * from bifMovie M, bifArtist A where M.movieID = A.movID and M.movieID = {0}", movID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _movie = new Movie(
                        (int)(dr["movieID"]),
                        (string)(dr["title"]),
                        (string)(dr["description"]),
                        (string)(dr["genre"]),
                        (string)(dr["timeLenght"]),
                        (string)(dr["director"]),
                        (string)(dr["picture"]));

                    //ARTISTER KOMMER IKKE MED I MOVIE::: FIKS :)
                }
                dr.Close();
            }
            return _movie;
        }


        public void setMovieState(int movID, bool state)
        {
            int intState = convertState(state);

            string sql = string.Format("Update bifMovie Set (active = {0}) where movieId = {1}", intState, movID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        private int convertState(bool state)
        {
            int convertState = 0;
            if (state)
            {
                convertState = 1;
            }
            return convertState;
        }
        #endregion

        #region CinemaHall
        public void NewCinemaHall(int showID)
        {
            try
            {
                string sql = string.Format("Insert into bifCinemaHall" +
                "(showID) Values" +
                "('{0}')", showID);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se3)
            {
                Console.WriteLine("!Fejl inserting cinemahall " + se3);
            }
        }

        public CinemaHall GetOneCinemaHall(int cinemaID)
        {
            try
            {
                CinemaHall cinemaHall = new CinemaHall();
                string sql = string.Format("Select * from bifCinemaHall where cinID = '{0}'", cinemaID);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        cinemaHall = (new CinemaHall(
                                    (int)(dr["cinID"]),
                                    (int)(dr["showID"])));
                    }
                    dr.Close();
                }
                return cinemaHall;
            }
            catch (SqlException se)
            {
                Console.WriteLine("SQL Exception: " + se);
                return null;
            }

        }

        public void DeleteCinemaHall(int cinemaID)
        {
            try
            {
                string sql = string.Format("Delete from bifCinemaHall where cinID  = '{0}'", cinemaID);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {

                Console.WriteLine("Sorry! Cinemahall not deleted! \n" + e);
            }


        }

        public void UpdateCinemaHall(int cinemaID, int showID)
        {
            try
            {
                string sql = string.Format("Update bifCinemaHall Set (showID = '{0}') Where cinID = {1}"
                , showID, cinemaID);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Sowwy! Error in updating Cinemahall!\n" + e);
            }
        }

        public List<CinemaHall> AllCinemas()
        {
            List<CinemaHall> inv = new List<CinemaHall>();
            string sql = "Select * From bifCinemaHall";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new CinemaHall(
                        (int)(dr["cinID"]),
                        (int)(dr["showID"])

                ));
                }
                dr.Close();
            }
            return inv;
        }
        #endregion

        #region Show
        public void NewShow(DateTime date, int movID)
        {
            try
            {
                string sql = string.Format("Insert into bifShow" +
                "(date, movID) Values" +
                "('{0}', '{1}')", date, movID);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se3)
            {
                Console.WriteLine("!Fejl inserting show " + se3);
            }
        }

        public Show GetOneShow(int showID)
        {
            Show show = new Show();
            string sql = string.Format("Select * from bifShow where showID = '{0}'", showID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    show = (new Show(
                                (int)(dr["showID"]),
                                (DateTime)(dr["date"]),
                                (int)(dr["movID"])));
                }
                dr.Close();
            }
            return show;
        }

        public void DeleteShow(int showID)
        {
            try
            {
                string sql = string.Format("Delete from bifShow where showID  = '{0}'", showID);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {

                Console.WriteLine("Sorry! Show's not deleted! \n" + se);
            }


        }

        public void UpdateShow(int showID, DateTime date, int movieID)
        {
            try
            {
                string sql = string.Format("Update bifShow Set (date = '{0}', movieID = '{1}') Where showID = {2}"
                , date, movieID, showID);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Sowwy! Error in updating Show!\n" + e);
            }
        }

        public List<Show> AllShows()
        {
            List<Show> inv = new List<Show>();
            string sql = "Select * From bifShow";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Show(
                        (int)(dr["showID"]),
                        (DateTime)(dr["date"]),
                        (int)(dr["movID"])

                ));
                }
                dr.Close();
            }
            return inv;
        }
        #endregion

        #region Bookings
        public void NewBooking(DateTime date, int cinID, int showID, int perID, int seatNo)
        {
            try
            {
                string sql = string.Format("Insert into bifBooking" +
                     "(title,description,genre,timeLength,director,picture) Values" +
                     "('{0}', '{1}', '{2}', '{3}','{4}')", date, cinID, showID, perID, seatNo);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Booking error " + se);

            }
        }

        public Booking GetOneBooking(int bookID)
        {
            Booking booking = new Booking();
            string sql = string.Format("Select * from bifBooking where bookID = '{0}'", bookID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    booking = (new Booking(
                                (int)(dr["bookID"]),
                                (DateTime)(dr["date"])
                                ));
                }
                dr.Close();
            }
            return booking;
        }

        public void DeleteBooking(int bookID)
        {
            try
            {
                string sql = string.Format("Delete from bifBooking where bookID  = '{0}'", bookID);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {

                Console.WriteLine("Sorry! Booking is not deleted! \n" + se);
            }


        }

        public void UpdateBooking(int bookID, DateTime date, int cinID, int showID, int perID, int seatNo)
        {
            try
            {
                string sql = string.Format("Update bifBooking Set (date = '{0}', cinID = '{1}', showID = '{2}', perID = '{3}', seatNo = '{4}') Where bookID = {5}"
                , date, cinID, showID, perID, seatNo, bookID);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Sowwy! Error in updating Booking!\n" + e);
            }
        }

        public List<Booking> AllBookings()
        {
            List<Booking> inv = new List<Booking>();
            string sql = "Select * From bifBooking";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new Booking(
                        (int)(dr["bookID"]),
                        (DateTime)(dr["date"])

                ));
                }
                dr.Close();
            }
            return inv;
        }
        #endregion
    }
}